/* Program name: auto_debt.sas
   Objective: create an estimate of vehicle-related debt and student loan debt.;
   This code requires the clo variable, which is created in clean_tradeline97.sas, so cannot 
   be run on the raw tradeline files;
   Note: HARDCODED DATES BELOW;
*/
libname in '/data';
libname scorein '/data';
%include "/data/tu_formats.sas";
%include "/data/06macros.sas";

data temp (keep = int teditseq term hicredit acctype loantypt loantype acctbal ecoa crdtlim cll auto_: mortgage_: dtveri student_: ccard_: cctot_: install_: lcred_: unsec_: secgoods_: lease_:  dtclose dtopen paypat mop rename=(dtclose=dtclo));
  set in.cleantrade97;
  
  /*'coo' is closed account or inactive/terminated in some way*/
  if clo eq 1 or dispcd in ('IA','INA','FTS','FTB','ETS','ETB','ETI') then coo = 1;
  else coo = 0;

  if dtclose gt 0 or coo eq 1 then cll = 1;
  else if acctype in ('I', 'M') and acctbal le 0 and mop eq 1 then cll = 1; /*paid installment or mortgage*/
  else cll = 0;

  if acctbal lt 0 then acctbal = .;

/************************ADDED CODE 5/2019*****************/


/* installment loans */
  if loantypt in('IS') then do;
     loantype = "INSTALL"; 
     install_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     install_hist_ontime = paymop1_24;
     install_hist_3060 = pay3059_24;
     install_hist_6090 = pay6089_24;
     install_hist_90120 = pay90119_24;
     install_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then install_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199607 then do;
	      if acctbal eq 0 then install_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(install);
	end;
     end;
  end;

/* line of credit */
  if loantypt in('LC') then do;
     loantype = "LCRED"; 
     lcred_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lcred_hist_ontime = paymop1_24;
     lcred_hist_3060 = pay3059_24;
     lcred_hist_6090 = pay6089_24;
     lcred_hist_90120 = pay90119_24;
     lcred_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lcred_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199607 then do;
	      if acctbal eq 0 then lcred_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lcred);
	end;
     end;
  end;


/* Unsecured */
  if loantypt in('US') then do;
     loantype = "UNSEC"; 
     unsec_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     unsec_hist_ontime = paymop1_24;
     unsec_hist_3060 = pay3059_24;
     unsec_hist_6090 = pay6089_24;
     unsec_hist_90120 = pay90119_24;
     unsec_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then unsec_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199607 then do;
	      if acctbal eq 0 then unsec_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(unsec);
	end;
     end;
  end;

/* Household goods secured & collateral */
  if loantypt in('SO','SH') then do;
     loantype = "SECGOODS"; 
     secgoods_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     secgoods_hist_ontime = paymop1_24;
     secgoods_hist_3060 = pay3059_24;
     secgoods_hist_6090 = pay6089_24;
     secgoods_hist_90120 = pay90119_24;
     secgoods_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then secgoods_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199607 then do;
	      if acctbal eq 0 then secgoods_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(secgoods);
	end;
     end;
  end;


/* lease */
  if loantypt in('LE') then do;
     loantype = "LEASE"; 
     lease_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lease_hist_ontime = paymop1_24;
     lease_hist_3060 = pay3059_24;
     lease_hist_6090 = pay6089_24;
     lease_hist_90120 = pay90119_24;
     lease_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lease_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199607 then do;
	      if acctbal eq 0 then lease_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lease);
	end;
     end;
  end;


/************************STOP ADDED CODE 5/2019*****************/

  /* auto_loans */
  if loantypt in('AU','AL','AR','AT','CA','MB','MT','RV') then do;
     loantype = "AUTO"; 
     auto_open = 1;

     /*count number of payments current/delinquent in past 48 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline97.sas*/
     auto_hist_ontime = paymop1;
     auto_hist_3060 = pay3059;
     auto_hist_6090 = pay6089;
     auto_hist_90120 = pay90119;
     auto_hist_120plus	= pay120up;
 				 
     if (cll eq 1 or acctbal le 0) then auto_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 then do;
	if acctbal eq 0 then auto_clo = 1;
	if acctbal gt 0 then do;
	   %recentcount(auto);
	end;
     end;
  end;

  
  /* total student debt*/
  if loantypt ='ST' then do;
     loantype="STUD"; 
     student_open = 1;

     student_hist_ontime = paymop1;
     student_hist_3060 = pay3059;
     student_hist_6090 = pay6089;
     student_hist_90120 = pay90119;
     student_hist_120plus = pay120up;

     if (cll eq 1 or acctbal le 0) then studen_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 then do;
	      if acctbal eq 0 then student_clo = 1;
        if acctbal gt 0 then do;
	   %recentcount(student);	 
	end;
     end;
  end;

  /* total mortgage debt*/
  if loantypt in ('CV', 'FH', 'FI', 'FR', 'HE', 'HI', 'PI', 'RE', 'RL', 'RM', 'RT', 'SM', 'VA', 'VM','UK',' ') and acctype eq 'M' then do;
     loantype = "MORT"; 
     mortgage_open = 1;

     mortgage_hist_ontime = paymop1;
     mortgage_hist_3060 = pay3059;
     mortgage_hist_6090 = pay6089;
     mortgage_hist_90120 = pay90119;
     mortgage_hist_120plus = pay120up;

     if (cll eq 1 or acctbal le 0) then mortgage_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 then do;
	      if acctbal eq 0 then mortgage_clo = 1;
        if acctbal gt 0 then do;
	   %recentcount(mortgage);	 
	end;
     end;
  end;


  /* credit card debt (secured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC') then do;
     loantype = "CCAR";
     
     if ecoa = "I" then do;
     	ccard_indiv_open = 1;
     	ccard_indiv_hist_ontime = paymop1;
	    ccard_indiv_hist_3060 = pay3059;
     	ccard_indiv_hist_6090 = pay6089;
     	ccard_indiv_hist_90120 = pay90119;
     	ccard_indiv_hist_120plus = pay120up;
     end;

     if ecoa = "C" then do;
     	ccard_joint_open = 1;
	    ccard_joint_hist_ontime = paymop1;
	    ccard_joint_hist_3060 = pay3059;
     	ccard_joint_hist_6090 = pay6089;
     	ccard_joint_hist_90120 = pay90119;
     	ccard_joint_hist_120plus = pay120up;
     end;

     if ecoa in ("M", "S", "C") then do;
     	ccard_cosign_open = 1;
	    ccard_cosign_hist_ontime = paymop1;
	    ccard_cosign_hist_3060 = pay3059;
     	ccard_cosign_hist_6090 = pay6089;
     	ccard_cosign_hist_90120 = pay90119;
     	ccard_cosign_hist_120plus = pay120up;
     end;

     if ecoa = "A" then do;
     	ccard_notliab_open = 1;
	    ccard_notliab_hist_ontime = paymop1;
	    ccard_notliab_hist_3060 = pay3059;
     	ccard_notliab_hist_6090 = pay6089;
     	ccard_notliab_hist_90120 = pay90119;
     	ccard_notliab_hist_120plus = pay120up;
     end;

     if ecoa in ("A", "P") then do;
     	ccard_maybeliab_open = 1;
	    ccard_maybeliab_hist_ontime = paymop1;
	    ccard_maybeliab_hist_3060 = pay3059;
     	ccard_maybeliab_hist_6090 = pay6089;
     	ccard_maybeliab_hist_90120 = pay90119;
     	ccard_maybeliab_hist_120plus = pay120up;
     end;

     * individual account;
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then ccard_indiv_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa = "I" then do;
     	%recentcount(ccard_indiv);	 
     end;

     * joint account;
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then ccard_joint_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa = "C" then do;
     	%recentcount(ccard_joint);	 
     end;
     
     * cosigner;
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then ccard_cosign_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa in ("M", "S", "C") then do;
     	%recentcount(ccard_cosign);	 
     end; 

     * not liable;
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then ccard_notliab_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa in ("A") then do;
     	%recentcount(ccard_notliab);
     end; 

     * maybe liable;
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A","P") then ccard_maybeliab_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa in ("A", "P") then do;
     	%recentcount(ccard_maybeliab);
     end; 
  end;

  /* credit card total debt (secured + unsecured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC','SC') then do;

    if ecoa = "I" then do;
     	cctot_indiv_open = 1;
     	cctot_indiv_hist_ontime = paymop1;
	    cctot_indiv_hist_3060 = pay3059;
     	cctot_indiv_hist_6090 = pay6089;
     	cctot_indiv_hist_90120 = pay90119;
     	cctot_indiv_hist_120plus = pay120up;
     end;

     if ecoa = "C" then do;
     	cctot_joint_open = 1;
	    cctot_joint_hist_ontime = paymop1;
	    cctot_joint_hist_3060 = pay3059;
     	cctot_joint_hist_6090 = pay6089;
     	cctot_joint_hist_90120 = pay90119;
     	cctot_joint_hist_120plus = pay120up;
     end;

     if ecoa in ("M", "S", "C") then do;
     	cctot_cosign_open = 1;
	    cctot_cosign_hist_ontime = paymop1;
	    cctot_cosign_hist_3060 = pay3059;
     	cctot_cosign_hist_6090 = pay6089;
     	cctot_cosign_hist_90120 = pay90119;
     	cctot_cosign_hist_120plus = pay120up;
     end;

     if ecoa = "A" then do;
     	cctot_notliab_open = 1;
	    cctot_notliab_hist_ontime = paymop1;
	    cctot_notliab_hist_3060 = pay3059;
     	cctot_notliab_hist_6090 = pay6089;
     	cctot_notliab_hist_90120 = pay90119;
     	cctot_notliab_hist_120plus = pay120up;
     end;

     if ecoa in ("A", "P") then do;
     	cctot_maybeliab_open = 1;
	    cctot_maybeliab_hist_ontime = paymop1;
	    cctot_maybeliab_hist_3060 = pay3059;
     	cctot_maybeliab_hist_6090 = pay6089;
     	cctot_maybeliab_hist_90120 = pay90119;
     	cctot_maybeliab_hist_120plus = pay120up;
     end;

     * individual account;
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then cctot_indiv_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa = "I" then do;
	   %recentcount(cctot_indiv);	 
     end;

     * joint account;
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then cctot_joint_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa = "C" then do;
	   %recentcount(cctot_joint);	 
     end;

     * cosigner;
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then cctot_cosign_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa in ("M", "S", "C") then do;
	   %recentcount(cctot_cosign);	
     end; 

     * not liable;
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then cctot_notliab_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa in ("A") then do;
	   %recentcount(cctot_notliab);
     end; 

     * maybe liable;
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A","P") then cctot_maybeliab_clo = 1;
     if cll ne 1 and acctbal ge 0 and dtveri ge 199607 and ecoa in ("A", "P") then do;
	   %recentcount(cctot_maybeliab);
     end; 
  end;

run;

proc sort data = temp;
  by teditseq;
run;
/*select variables that we want to get sum for collapsed dataset*/
proc sql;
     select name
     into :sumlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_debt' or name ? '_num' or name like '%_ontime' 
     or name like '_lim' or name like '%3060' or name like '%6090' or name like '%90120' 
     or name like '%120plus' or name like '%_worse'
     or name ? '_clo' or name ? '_open');
     quit;
/*select lot variables that we want to get min (choose earliest date)*/
proc sql;
     select name 
     into :minlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_lot' or name ? '_f120');
     quit;
/*collapse the dataset to total accounts of debt per person*/
 proc means data = temp noprint;
  by teditseq;
  var install: lcred: unsec: secgoods: lease: auto: trans: student: mortgage: ccard: cctot:;
  output out=rev_install_type97 min(&minlist)= sum(&sumlist)=;
run;

data rev_install_type97;
    set rev_install_type97;
    drop _type_ _freq_;

    ccard_indiv_joint_debt=sum(ccard_indiv_debt,.5*ccard_cosign_debt);
    cctot_indiv_joint_debt=sum(cctot_indiv_debt,.5*cctot_cosign_debt);

ccard_indiv_joint_lim=sum(ccard_indiv_lim,.5*ccard_cosign_lim);
    cctot_indiv_joint_lim=sum(cctot_indiv_lim,.5*cctot_cosign_lim);

run;

proc sort data=rev_install_type97; by teditseq; run;
proc sort data=temp; by teditseq; run;

data temp_new;


merge temp (in = a keep= teditseq dtopen dtveri dtclo terms hicredit install_clo install_open lcred_clo lcred_open unsec_clo unsec_open secgoods_clo secgoods_open lease_clo lease_open  student_clo student_open mortgage_clo mortgage_open auto_clo auto_open loantype student_debt int crdtlim acctbal ecoa
                    rename=(student_debt=student_debt_tl student_clo=student_clo_tl student_open=student_open_tl 
install_clo=install_clo_tl 
install_open = install_open_tl
lcred_clo = lcred_clo_tl
lcred_open = lcred_open_tl 
unsec_clo = unsec_clo_tl 
unsec_open = unsec_open_tl 
secgoods_clo = secgoods_clo_tl
secgoods_open = secgoods_open_tl
lease_clo = lease_clo_tl
lease_open = lease_open_tl 
terms=terms_tl 
hicredit=hicredit_tl 
mortgage_clo=mortgage_clo_tl 
                            mortgage_open=mortgage_open_tl auto_clo=auto_clo_tl auto_open=auto_open_tl crdtlim=crdtlim_tl acctbal=acctbal_tl ecoa=ecoa_tl))


 rev_install_type97 (in = b);
by teditseq;      
  
if a; 
run;

/* pull roll up & locational variables from SCORE dataset */
/* we rename the variables to chVARNAME in order to convert them from character to numeric*/
/* we do this because the variables in 03-08 are numeric, and we want them all formatted the same*/
%let renamelist=CBSA=chCBSA COUNTY=chCOUNTY LAT=chLAT LONG=chLONG STATE=chSTATE TRACT=chTRACT ZIP=ZIP97;

data roll_up97(drop=AT01 ch:);
     set scorein.score97 (keep= teditseq RE28 RE34 AT33 AT28 ON33 MT33 IN33 RE33 G041-G045 G057-G071 G082-G087 CBSA 
     	 		 	COUNTY LAT LONG STATE TRACT ZIP score AT01 
     	 		 	G104 G093-G094 G096 S059 S063-S064
		          rename=(&renamelist));
     rename score=tr_am;
     /*if AT01 ne 0;	 we want to include obs with 0 tradelines from score per conversation
     	      	 	 with alvaro - AN 06.20.13 */
     CBSA=input(chCBSA,8.);
     COUNTY=input(chCOUNTY,8.);
     LAT=input(chLAT,8.);
     LONG=input(chLONG,8.);
     STATE=input(chSTATE,8.);
     TRACT=input(substr(chTRACT,1,4),8.);
     SUFFIX=input(substr(chTRACT,5,2),8.);     
run;

data in.roll_trades97(rename=(teditseq=teditseq97));
     merge roll_up97 rev_install_type97;
     by teditseq;
run;

proc contents data=in.roll_trades97;
run;

proc print data=in.roll_trades97(obs=30);
run;

data in.roll_trades97(rename=(teditseq=teditseq97));
     merge roll_up97
           temp_new ;
     by teditseq;  /*merge should be conconditional on in=a or in=b*/
run;



